package com.example.dps;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TipsDBHelper extends SQLiteOpenHelper{
	
	static final String tag = "TipsDBHelper";
	
	SQLiteDatabase db;
	
	int tableSize = 0;
	
	/**********************************************************************
	 * Database Variables
	 */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "DownPaymentSaver.db";
    /********************************************************************/
    
    public static final String TABLE_NAME = "Tips";
    
    public static final String COLUMN_TIP_ID = "_id";
    public static final String COLUMN_TIP_TITLE = "title";
    public static final String COLUMN_TIP_TEXT = "text";
    
    public static final String CREATE_TIPS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
    		"(" +
    		COLUMN_TIP_ID + " INTEGER PRIMARY KEY, " +
    		COLUMN_TIP_TITLE + " VARCHAR(80), "+
    		COLUMN_TIP_TEXT + " TEXT"+
    		");";
    		
    
    
    public void createTable(){
    	this.db = getWritableDatabase();
    	
    	this.db.execSQL(CREATE_TIPS_TABLE);
    	
    	this.db.close();
    	
    	
    	if(tableIsEmpty()) initTipsTable();
    	
    	
    }

	public TipsDBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		createTable();
		
		this.tableSize = getTableSize();
		
		
		
	}

	
	/**
	 * retrieve a Tip object corresponding to the given ID
	 * @param id the tip ID
	 * @return
	 * @throws Exception if the cursor has no results, throws exception
	 */
	public Tip getTip(int id){
		
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_TIP_ID + " = ?";
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery(query, new String[] {String.valueOf(id)});
		
		Tip tip = null;
		try {
			tip = new Tip(result);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		result.close();
		this.db.close();
		
		return tip;
		
	}
	
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	
	public void initTipsTable(){
		
		addTip("Savings Tip:", "Automate your savings. With regular contributes over time your savings build up quickly. Automatic deposits make it easy to stay on track.");
		
		addTip("Savings Tip:", "Try the 30 day rule. Whenever you're tempted to make an unnecessary purchase, wait 30 days and then see if you still feel as strongly about it.");
		
		addTip("Home Buying Tip:", "Shop for your mortgage. By comparing interest rates, application fees and other costs, you can save a considerable amount of money.");
		
		addTip("Mortgage Loan Tip:", "With an adjustable rate mortgage (ARM) your payments go up when interest rates rise. A fixed rate will keep your monthly payments the same for the term (usually 30 years.)");
	
		addTip("Savings Tip:", "Instead of going to the mall, go for a hike or a bike ride. Instead of going to a restaurant, have a candle-lit dinner at home.");
		
		addTip("Savings Tip:", "Bag it. You can save over $600 a year if you take a bagged lunch to work rather than buying lunch each day.");
		
		addTip("Savings Tip:", "If you smoke, quit now! You already know that you're waisting your money as well as damaging your health.");
		
		addTip("Mortgage Tip:", "Avoid the PMI. If you put down 20% or more you can avoid paying for private mortgage insurance.");
		
		addTip("Savings Tip:", "Go into \"saving mode.\" If you keep your savings goal a priority, and continually look for ways to reduce expenses, you might be amazed at your results.");
		
		addTip("Savings Tip:", "Try a \"stay-cation.\" Instead of taking a vacation, relax at home, take day-trips, or visit with friends or family. Better yet, earn a few extra dollars if you can.");
		
		addTip("Savings Tip:", "Avoid lottery tickets. The odds are against you, so don't start thinking there's an instant solution. It will almost always put you further from your goal. Save that dollar.");
		
		addTip("Savings Tip:", "Don't look! Avoid browsing catalogs, retail websites or walking through stores. These are all designed to tempt you to spend your money.");
		
		addTip("Savings Tip:", "When you have to buy an item, do your research. You can often find lower prices online for tires, eyeglasses, diapers and electronics, but stores can be lower on some items.");
		
		addTip("Savings Tip:", "Make a grocery list. Plan your meals and stick to your list at the grocery store. You can save a lot of money by avoiding the extra items you don't need.");
		
		addTip("Home Buying Tip:", "Find out about government programs, mortgage assistance, free mortgage counseling and other helpful information right here.");

		
	
	}
	
	public boolean tableIsEmpty(){
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery("SELECT Count(*) FROM " + TABLE_NAME, null);
		
		result.moveToFirst();
		
		int count = result.getInt(0);
		
		result.close();
		this.db.close();
		
		if(count > 0) return false;
		else return true;
		
	}
	
	public int getTableSize(){
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery("SELECT Count(*) FROM " + TABLE_NAME, null);
		
		result.moveToFirst();
		
		int count = result.getInt(0);
		
		result.close();
		this.db.close();
		
		return count;
		
	}
	
	public void addTip(String title, String text){
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TIP_TEXT, text);
		values.put(COLUMN_TIP_TITLE, title);
		
		this.db.insert(TABLE_NAME, null, values);
		
		
	}
	
	public Tip getTip(){
		
		SQLiteDatabase db = getReadableDatabase();
		
		
		int id = (int) (this.tableSize*Math.random())+1;
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_TIP_ID + " = ?";
		
		Cursor result = db.rawQuery(query, new String[]{ String.valueOf(id)});
		
		Tip tip = null;
		try {
			tip = new Tip(result);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		result.close();
		db.close();
		
		return tip;
		
	}
	
}
